﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CIB_DD_TD : DA_Matrix
    {
        protected string wmgCUR = @"'AUD','CAD','CHF','EUR','GBP','HKD','JPY','NZD','SEK'
							,'SGD','THB','USD','ZAR','CNY' ";

        protected int ymCount = 0;

        public int GetYMCount()
        { return ymCount; }

        public override string[] GetDistinctField()
        {
            return new string[] { "[Curr Code]" };
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_year"></param>
        /// <param name="p_curr1">要算佔比的幣別</param>
        /// <param name="p_curr2">要算利差的幣別</param>
        /// <returns></returns>
        public DataSet GetSummary(string p_year, string p_curr1, string p_curr2)
        {
            //C_TD4_TD_FX AS A WITH(INDEX(IX_C_TD4_TD_YM))
            //C_DD4_DD_FX AS A WITH(INDEX(IX_C_DD4_DD_YM))
            //外活[C_DD4_DD_FX]：DD 、外定[C_TD4_TD_FX]：TD
            string ddTblName, tdTblName, ddTblNameIdx, tdTblNameIdx;
            ddTblName = "C_DD4_DD_FX";
            tdTblName = "C_TD4_TD_FX";
            ddTblNameIdx = "C_DD4_DD_FX AS A WITH(INDEX(IX_C_DD4_DD_YM))";
            tdTblNameIdx = "C_TD4_TD_FX AS A WITH(INDEX(IX_C_TD4_TD_YM))";

            DataTable  DD_BalFtp, TD_BalFtp;

            #region 計算外幣的利息支出、FTP息
            DD_BalFtp = getDDTDTable(ddTblName, p_year
                , getBALFTPSQLStatement(ddTblNameIdx, p_year)
                , "外幣活"
                );

            TD_BalFtp = getDDTDTable(tdTblName, p_year
                , getBALFTPSQLStatement(tdTblNameIdx, p_year)
                , "外幣定"
                );
            #endregion

            DataSet ds = new DataSet();
            ds.Tables.Add(getDDTDTable(ddTblName, p_year
                , getOccupySQLStatement(ddTblNameIdx, p_year)
                , "外活佔比"));
            ds.Tables.Add(getDDTDTable(tdTblName, p_year
                , getOccupySQLStatement(tdTblNameIdx, p_year)
                , "外定佔比"
                ));
            ds.Tables.Add(DD_BalFtp);
            ds.Tables.Add(TD_BalFtp);
            ds.Tables.Add(getDDTDTable(ddTblName, p_year
                , getRateDiffSQLStatement(ddTblNameIdx, p_year)
                , "外活利差"
                ));
            ds.Tables.Add(getDDTDTable(tdTblName, p_year
                , getRateDiffSQLStatement(tdTblNameIdx, p_year)
                , "外定利差"
                ));
            ds.Tables.Add(getBALFTP(DD_BalFtp, "外活FTP利差"));
            ds.Tables.Add(getBALFTP(TD_BalFtp, "外定FTP利差"));

            return ds;
        }
        public DataSet GetSummary(string[] p_yms, string p_curr1, string p_curr2)
        {
            //C_TD4_TD_FX AS A WITH(INDEX(IX_C_TD4_TD_YM))
            //C_DD4_DD_FX AS A WITH(INDEX(IX_C_DD4_DD_YM))
            //外活[C_DD4_DD_FX]：DD 、外定[C_TD4_TD_FX]：TD
            string ddTblName, tdTblName, ddTblNameIdx, tdTblNameIdx;
            ddTblName = "C_DD4_DD_FX";
            tdTblName = "C_TD4_TD_FX";
            ddTblNameIdx = "C_DD4_DD_FX AS A WITH(INDEX(IX_C_DD4_DD_YM))";
            tdTblNameIdx = "C_TD4_TD_FX AS A WITH(INDEX(IX_C_TD4_TD_YM))";

            DataTable DD_BalFtp, TD_BalFtp;

            #region 計算外幣的利息支出、FTP息
            DD_BalFtp = getDDTDTable(ddTblName, p_yms
                , getBALFTPSQLStatement(ddTblNameIdx, p_yms)
                , "外幣活"
                );

            TD_BalFtp = getDDTDTable(tdTblName, p_yms
                , getBALFTPSQLStatement(tdTblNameIdx, p_yms)
                , "外幣定"
                );
            #endregion

            DataSet ds = new DataSet();
            ds.Tables.Add(getDDTDTable(ddTblName, p_yms
                , getOccupySQLStatement(ddTblNameIdx, p_yms)
                , "外活佔比"));
            ds.Tables.Add(getDDTDTable(tdTblName, p_yms
                , getOccupySQLStatement(tdTblNameIdx, p_yms)
                , "外定佔比"
                ));
            ds.Tables.Add(DD_BalFtp);
            ds.Tables.Add(TD_BalFtp);
            ds.Tables.Add(getDDTDTable(ddTblName, p_yms
                , getRateDiffSQLStatement(ddTblNameIdx, p_yms)
                , "外活利差"
                ));
            ds.Tables.Add(getDDTDTable(tdTblName, p_yms
                , getRateDiffSQLStatement(tdTblNameIdx, p_yms)
                , "外定利差"
                ));
            ds.Tables.Add(getBALFTP(DD_BalFtp, "外活FTP利差"));
            ds.Tables.Add(getBALFTP(TD_BalFtp, "外定FTP利差"));
            return ds;
        }

        /// <summary>
        /// WMG
        /// </summary>
        /// <param name="p_year"></param>
        /// <param name="p_curr1"></param>
        /// <param name="p_curr2"></param>
        /// <returns></returns>
        public DataSet GetWMGSummary(string p_year, string p_curr1, string p_curr2)
        {
            string ddTblName, tdTblName;
            ddTblName = "外匯活期";
            tdTblName = "外匯定期";
            DataTable DD_BalFtp, TD_BalFtp;

            #region 計算外幣的利息支出、FTP息
            DD_BalFtp = getDDTDTable("WMG001", p_year
                , getWMGBALFTPSQLStatement(ddTblName, p_year)
                , "外幣活", "年月"
                );

            TD_BalFtp = getDDTDTable("WMG001", p_year
                , getWMGBALFTPSQLStatement(tdTblName, p_year)
                , "外幣定", "年月"
                );
            #endregion

            DataSet ds = new DataSet();
            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGOccupySQLStatement(ddTblName, p_year)
                , "外活佔比", "年月"));

            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGOccupySQLStatement(tdTblName, p_year)
                , "外定佔比", "年月"
                ));
            ds.Tables.Add(DD_BalFtp);
            ds.Tables.Add(TD_BalFtp);
            //計算利差
            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGRateDiffSQLStatement(ddTblName, p_year)
                , "外活利差", "年月"
                ));
            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGRateDiffSQLStatement(tdTblName, p_year)
                , "外定利差", "年月"
                ));
            ds.Tables.Add(getBALFTP(DD_BalFtp,"外活FTP利差"));
            ds.Tables.Add(getBALFTP(TD_BalFtp, "外定FTP利差"));
            return ds;
        }
        public DataSet GetWMGSummary(string[] p_year, string p_curr1, string p_curr2)
        {
            string ddTblName, tdTblName;
            ddTblName = "外匯活期";
            tdTblName = "外匯定期";
            DataTable DD_BalFtp, TD_BalFtp;
            string[] formats = { "{0}", "{0}" };
            #region 計算外幣的利息支出、FTP息
            DD_BalFtp = getDDTDTable("WMG001", p_year
                , getWMGBALFTPSQLStatement(ddTblName, p_year)
                , "外幣活", "年月", formats
                );

            TD_BalFtp = getDDTDTable("WMG001", p_year
                , getWMGBALFTPSQLStatement(tdTblName, p_year)
                , "外幣定", "年月", formats
                );
            #endregion

            DataSet ds = new DataSet();
            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGOccupySQLStatement(ddTblName, p_year)
                , "外活佔比", "年月", formats));

            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGOccupySQLStatement(tdTblName, p_year)
                , "外定佔比", "年月", formats
                ));
            ds.Tables.Add(DD_BalFtp);
            ds.Tables.Add(TD_BalFtp);
            //計算利差
            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGRateDiffSQLStatement(ddTblName, p_year)
                , "外活利差", "年月", formats
                ));
            ds.Tables.Add(getDDTDTable("WMG001", p_year
                , getWMGRateDiffSQLStatement(tdTblName, p_year)
                , "外定利差", "年月", formats
                ));
            ds.Tables.Add(getBALFTP(DD_BalFtp, "外活FTP利差"));
            ds.Tables.Add(getBALFTP(TD_BalFtp, "外定FTP利差"));
            return ds;
        }

        /// <summary>
        ///  找出 ZZZ 因為這是FTP利差
        /// </summary>
        /// <param name="p_dt"></param>
        /// <param name="p_dtName"></param>
        /// <returns></returns>
        protected DataTable getBALFTP(DataTable p_dt,string p_dtName)
        {
            IEnumerable<DataRow> query = from dd in p_dt.AsEnumerable()
                                         where dd.Field<string>("幣別").Equals("ZZZ")
                                         select dd;
            System.Data.DataTable dtZZZ = query.CopyToDataTable<DataRow>();
            dtZZZ.TableName = p_dtName;
            return dtZZZ;
        }

        protected DataTable getDDTDTable(string p_tblName, string[] p_year
            , string p_sql, string p_dtName)
        {
            string[] formats = { "{0}", "{0}" };
            return getDDTDTable(p_tblName, p_year, p_sql, p_dtName
                , "YYYYMM",formats);
        }

        protected DataTable getDDTDTable(string p_tblName, string p_year
            , string p_sql, string p_dtName)
        {
            return getDDTDTable(p_tblName, p_year, p_sql, p_dtName, "YYYYMM");
        }

        protected DataTable getDDTDTable(string p_tblName, string p_year
            , string p_sql, string p_dtName, string p_fld)
        {
            string[] years = {p_year,p_year};
            string[] formats = {"{0}01","{0}12"};
            return getDDTDTable(p_tblName, years, p_sql, p_dtName, p_fld, formats);
        }

        protected DataTable getDDTDTable(string p_tblName, string[] p_year
                    , string p_sql, string p_dtName, string p_fld
            ,string[] p_format)
        {
            DataTable dt;
            tableName = p_tblName;
            ResetCriteria();

            //FillFieldList(new string[] { string.Format("{0}01", p_year[0]) }
            //    , new string[] { "@YM1" }, p_fld);
            //FillFieldList(new string[] { string.Format("{0}12", p_year[1]) }
            //                , new string[] { "@YM2" }, p_fld);
            FillFieldList(new string[] { string.Format(p_format[0], p_year[0]) }
              , new string[] { "@YM1" }, p_fld);
            FillFieldList(new string[] { string.Format(p_format[1], p_year[1]) }
                            , new string[] { "@YM2" }, p_fld);

            AddParameter(p_sql);
            dt = getData();
            dt.TableName = p_dtName;
            return dt;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_year">西元年</param>
        /// <param name="p_string">要產生的字串</param>
        /// <param name="p_day">是否要加入當月中的天數</param>
        /// <returns></returns>
        protected string getTwelveMonth(string p_year, string p_string, bool p_day)
        {
            /*
             * ,MAX(CASE WHEN A.YYYYMM = '{0}{1}' THEN A.[PERCENT]  END) [PERCENT_{0}{1}]
             * ,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.TOT  END) [TOTAL_{0}{1}]
             *  ,MAX(CASE WHEN A.YYYYMM ='201102' THEN (A.利息支出/A.FTP息*360/28) 
		            -(A.利息支出/A.AVG_BAL*12)  END) [201102DIFF]		
             */
            string[] months = {"01","02","03","04","05","06"
                               ,"07","08","09","10","11","12"};

            string twYear = (Convert.ToInt16(p_year) - 1911).ToString();
            StringBuilder sb = new StringBuilder();
            foreach (string mon in months)
            {
                if (p_day.Equals(false))
                {
                    sb.AppendFormat(p_string, p_year + mon, twYear + mon);
                }
                else
                {
                    sb.AppendFormat(p_string, p_year + mon
                        , getMonthDays(p_year, mon), twYear + mon);
                }

            }
            return sb.ToString();
        }

        /// <summary>
        /// 傳回任意日期區間的字串
        /// </summary>
        /// <param name="p_yms">開始YYYYMM、結束YYYYMM</param>
        /// <returns></returns>
        protected string getDateRange(string[] p_yms,string p_string,bool p_day)
        {
            ymCount = 0;
            DateTime d1 = Convert.ToDateTime(string.Format("{0}-{1}-01"
                ,p_yms[0].Substring(0,4)
                ,p_yms[0].Substring(4)));
            DateTime d2 = Convert.ToDateTime(string.Format("{0}-{1}-01"
                , p_yms[1].Substring(0, 4)
                , p_yms[1].Substring(4)));

            string[] months = {"","01","02","03","04","05","06"
                               ,"07","08","09","10","11","12"};

            StringBuilder sb = new StringBuilder();
            while (d1 <= d2)
            {
                string twYear = (d1.Year - 1911).ToString();
                if (p_day.Equals(false))
                {
                    sb.AppendFormat(p_string, d1.Year.ToString() 
                                    + months[d1.Month]
                        , twYear + months[d1.Month]);
                }
                else
                {
                    sb.AppendFormat(p_string, d1.Year.ToString() 
                                    + months[d1.Month]
                        , getMonthDays(d1.Year.ToString(), months[d1.Month])
                        , twYear + months[d1.Month]);
                }
                d1 = d1.AddMonths(1);
                ymCount++;
            }

            return sb.ToString();
        }

        /// <summary>
        /// 取得那個月份有多少天數
        /// </summary>
        /// <param name="p_year"></param>
        /// <param name="p_month"></param>
        /// <returns></returns>
        protected string getMonthDays(string p_year, string p_month)
        {
            DateTime dd = Convert.ToDateTime(string.Format("{0}-{1}-01"
                , p_year, p_month)).AddMonths(1);
            return dd.AddDays(-1).Day.ToString();
        }

        protected string getRateDiffSQLStatement(string p_tblName, string p_year
            ,string p_sql)
        {

            /* 這樣是一組
             * ,MAX(CASE WHEN A.YYYYMM ='201102' 
		THEN (A.利息支出/A.FTP息*360/28) 
		-(A.利息支出/A.AVG_BAL*12)  END) [201102DIFF]		
             * ↓先用string.Empty 來代替
             * {0}= A.幣別 IN ('AUD','EUR','HKD','JPY','NZD','USD')
             * {1}= TABLE NAME
             * {2}= 組合出來的欄位名
             */
            string fld = @"
,MAX(CASE WHEN A.YYYYMM ='{0}' 
		THEN (A.FTP息/A.AVG_BAL*360/{1})
		-(A.利息支出/A.AVG_BAL*12)   END) [{2}DIFF]		";
            fld = getTwelveMonth(p_year, fld, true);
            return string.Format(p_sql, string.Empty, p_tblName
                , fld,wmgCUR);
        }
        /// <summary>
        /// 計算利差的查詢指令
        /// </summary>
        /// <returns></returns>
        protected string getRateDiffSQLStatement(string p_tblName, string p_year)
        {

            return getRateDiffSQLStatement(p_tblName, p_year, @"
SELECT
	A.幣別
	{2}
FROM
(
	SELECT 
		ISNULL(A.YYYYMM,'999999') AS YYYYMM
		,ISNULL(a.[Curr Code],'ZZZ') AS 幣別
		,SUM([AVG Bal]) AS AVG_BAL
		,SUM(dbo.GetInterestExpense(A.[AVG Bal],a.[Int Rate],a.[Curr Code],LEFT(A.[Desc],4),A.YYYYMM)) AS 利息支出
		,SUM(dbo.GetFtpRate(a.[Curr Code],LEFT(A.[Desc],4),A.[AVG Bal],[Trsfr Rate],A.YYYYMM))AS FTP息
	FROM {1} 
    WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
	GROUP BY CUBE(A.YYYYMM,A.[CURR CODE])
)A
{0}
GROUP BY A.幣別
ORDER BY A.幣別");           
        }
        protected string getRateDiffSQLStatement(string p_tblName, string[] p_year)
        {

            /* 這樣是一組
             * ,MAX(CASE WHEN A.YYYYMM ='201102' 
		THEN (A.利息支出/A.FTP息*360/28) 
		-(A.利息支出/A.AVG_BAL*12)  END) [201102DIFF]		
             * ↓先用string.Empty 來代替
             * {0}= A.幣別 IN ('AUD','EUR','HKD','JPY','NZD','USD')
             * {1}= TABLE NAME
             * {2}= 組合出來的欄位名
             */
            string fld = @"
,MAX(CASE WHEN A.YYYYMM ='{0}' 
		THEN (A.FTP息/A.AVG_BAL*360/{1})
		-(A.利息支出/A.AVG_BAL*12)   END) [{2}DIFF]		";
            fld = getDateRange(p_year, fld, true);
            return string.Format(@"
SELECT
	A.幣別
	{2}
FROM
(
	SELECT 
		ISNULL(A.YYYYMM,'999999') AS YYYYMM
		,ISNULL(a.[Curr Code],'ZZZ') AS 幣別
		,SUM([AVG Bal]) AS AVG_BAL
		,SUM(dbo.GetInterestExpense(A.[AVG Bal],a.[Int Rate],a.[Curr Code],LEFT(A.[Desc],4),A.YYYYMM)) AS 利息支出
		,SUM(dbo.GetFtpRate(a.[Curr Code],LEFT(A.[Desc],4),A.[AVG Bal],[Trsfr Rate],A.YYYYMM))AS FTP息
	FROM {1} 
    WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
	GROUP BY CUBE(A.YYYYMM,A.[CURR CODE])
)A
{0}
GROUP BY A.幣別
ORDER BY A.幣別", string.Empty, p_tblName, fld);
        }
        
        /// <summary>
        /// WMG
        /// </summary>
        /// <param name="p_tblName"></param>
        /// <param name="p_year"></param>
        /// <returns></returns>
        protected string getWMGRateDiffSQLStatement(string p_tblName, string p_year)
        {
           return getRateDiffSQLStatement(p_tblName, p_year, @"
SELECT
	A.幣別
	{2}
FROM
(
	SELECT 
		ISNULL(A.年月,'999999') AS YYYYMM
		,ISNULL(a.幣別,'ZZZ') AS 幣別
		,SUM(AVG_BAL) AS AVG_BAL
		,SUM(misdb.dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月)) AS 利息支出
		,SUM(misdb.dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月))AS FTP息
	FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
    WHERE (A.年月  BETWEEN @YM1 AND @YM2)
      AND A.存款類別 = '{1}'
      AND A.分行別 = '999'
      AND A.幣別 IN({3})
	GROUP BY CUBE(A.年月,A.幣別)	
)A
{0}
GROUP BY A.幣別
ORDER BY A.幣別");
        }
        protected string getWMGRateDiffSQLStatement(string p_tblName, string[] p_year)
        {

            /* 這樣是一組
             * ,MAX(CASE WHEN A.YYYYMM ='201102' 
		THEN (A.利息支出/A.FTP息*360/28) 
		-(A.利息支出/A.AVG_BAL*12)  END) [201102DIFF]		
             * ↓先用string.Empty 來代替
             * {0}= A.幣別 IN ('AUD','EUR','HKD','JPY','NZD','USD')
             * {1}= TABLE NAME
             * {2}= 組合出來的欄位名
             */
            string fld = @"
,MAX(CASE WHEN A.YYYYMM ='{0}' 
		THEN (A.FTP息/A.AVG_BAL*360/{1})
		-(A.利息支出/A.AVG_BAL*12)   END) [{2}DIFF]		";
            fld = getDateRange(p_year, fld, true);
            return string.Format(@"
SELECT
	A.幣別
	{2}
FROM
(
	SELECT 
		ISNULL(A.年月,'999999') AS YYYYMM
		,ISNULL(a.幣別,'ZZZ') AS 幣別
		,SUM(AVG_BAL) AS AVG_BAL
		,SUM(misdb.dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月)) AS 利息支出
		,SUM(misdb.dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月))AS FTP息
	FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
    WHERE (A.年月  BETWEEN @YM1 AND @YM2)
      AND A.存款類別 = '{1}'
      AND A.分行別 = '999'
      AND A.幣別 IN({3})
	GROUP BY CUBE(A.年月,A.幣別)	
)A
{0}
GROUP BY A.幣別
ORDER BY A.幣別", string.Empty, p_tblName, fld,wmgCUR);
        }
        protected string getBALFTPSQLStatement(string p_tblName, string p_year
            , string p_sql)
        {
            string fld = @"
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.AVG_BAL END) [{2}均額]
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.利息支出 END) [{2}利息]	    
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.利息支出/A.AVG_BAL*12 END) [{2}利息_PER]		    
    --,0 [{2}AVG_BAL_PER]
	,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.FTP息 END) [{2}FTP]		
	,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.FTP息/A.AVG_BAL*360/{1} END) [{2}FTP_PER]	
";
            fld = getTwelveMonth(p_year, fld, true);
            return string.Format(p_sql, fld, p_tblName,wmgCUR);
        }
        /// <summary>
        /// 計算外幣的利息支出、FTP息
        /// </summary>
        /// <returns></returns>
        protected string getBALFTPSQLStatement(string p_tblName, string p_year)
        {
            /*這樣算一組，也就是一個月的份，還要按月份的天數去計算
             * 	
    ,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.AVG_BAL END) [201102AVB_BAL]
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出 END) [201102利息]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出/A.AVG_BAL*12 END) [201102AVGBAL_PER]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.FTP息 END) [201102FTP息]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出/A.FTP息*360/28 END) [201102FTP_PER]	
             * {0} ↑上面那一組，要產生出12個月份
             */
            return getBALFTPSQLStatement(p_tblName,p_year,@"
SELECT
	A.幣別
    {0}	
FROM
(
	SELECT 
		ISNULL(A.YYYYMM,'999999') AS YYYYMM
		,ISNULL(a.[Curr Code],'ZZZ') AS 幣別
		,SUM([AVG Bal]) AS AVG_BAL
		,SUM(dbo.GetInterestExpense(A.[AVG Bal],a.[Int Rate],a.[Curr Code],LEFT(A.[Desc],4),A.YYYYMM)) AS 利息支出
		,SUM(dbo.GetFtpRate(a.[Curr Code],LEFT(A.[Desc],4),A.[AVG Bal],[Trsfr Rate],A.YYYYMM))AS FTP息		
	  FROM {1} 
      WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
	GROUP BY CUBE(A.YYYYMM,A.[CURR CODE])
)A
GROUP BY A.幣別
ORDER BY A.幣別");
        }
        protected string getBALFTPSQLStatement(string p_tblName, string[] p_year)
        {
            /*這樣算一組，也就是一個月的份，還要按月份的天數去計算
             * 	
    ,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.AVG_BAL END) [201102AVB_BAL]
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出 END) [201102利息]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出/A.AVG_BAL*12 END) [201102AVGBAL_PER]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.FTP息 END) [201102FTP息]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出/A.FTP息*360/28 END) [201102FTP_PER]	
             * {0} ↑上面那一組，要產生出12個月份
             */
            string fld = @"
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.AVG_BAL END) [{2}均額]
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.利息支出 END) [{2}利息]	    
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.利息支出/A.AVG_BAL*12 END) [{2}利息_PER]		    
    --,0 [{2}AVG_BAL_PER]
	,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.FTP息 END) [{2}FTP]		
	,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.FTP息/A.AVG_BAL*360/{1} END) [{2}FTP_PER]	
";
            fld = getDateRange(p_year, fld, true);

            return string.Format(@"
SELECT
	A.幣別
    {0}	
FROM
(
	SELECT 
		ISNULL(A.YYYYMM,'999999') AS YYYYMM
		,ISNULL(a.[Curr Code],'ZZZ') AS 幣別
		,SUM([AVG Bal]) AS AVG_BAL
		,SUM(dbo.GetInterestExpense(A.[AVG Bal],a.[Int Rate],a.[Curr Code],LEFT(A.[Desc],4),A.YYYYMM)) AS 利息支出
		,SUM(dbo.GetFtpRate(a.[Curr Code],LEFT(A.[Desc],4),A.[AVG Bal],[Trsfr Rate],A.YYYYMM))AS FTP息		
	  FROM {1} 
      WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
	GROUP BY CUBE(A.YYYYMM,A.[CURR CODE])
)A
GROUP BY A.幣別
ORDER BY A.幣別
", fld, p_tblName);
        }

        /// <summary>
        /// WMG
        /// </summary>
        /// <param name="p_tblName"></param>
        /// <param name="p_year"></param>
        /// <returns></returns>
        protected string getWMGBALFTPSQLStatement(string p_tblName, string p_year)
        {
            return getBALFTPSQLStatement(p_tblName,p_year, @"
SELECT
	A.幣別
    {0}	
FROM
(
    SELECT 
		ISNULL(A.年月,'999999') AS YYYYMM
		,ISNULL(a.幣別,'ZZZ') AS 幣別
		,SUM(AVG_BAL) AS AVG_BAL
		,SUM(misdb.dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月)) AS 利息支出
		,SUM(misdb.dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月))AS FTP息
	  FROM wmg.dbo.wmg001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
      WHERE (A.年月  BETWEEN @YM1 AND @YM2)
      AND A.存款類別 = '{1}'
      AND A.分行別 = '999'
      AND A.幣別 IN({2})
	GROUP BY CUBE(A.年月,A.幣別)
)A
GROUP BY A.幣別
ORDER BY A.幣別");
        }
        protected string getWMGBALFTPSQLStatement(string p_tblName, string[] p_year)
        {
            /*這樣算一組，也就是一個月的份，還要按月份的天數去計算
             * 	
    ,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.AVG_BAL END) [201102AVB_BAL]
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出 END) [201102利息]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出/A.AVG_BAL*12 END) [201102AVGBAL_PER]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.FTP息 END) [201102FTP息]	
	,MAX(CASE WHEN A.YYYYMM ='201102' THEN A.利息支出/A.FTP息*360/28 END) [201102FTP_PER]	
             * {0} ↑上面那一組，要產生出12個月份
             */
            string fld = @"
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.AVG_BAL END) [{2}均額]
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.利息支出 END) [{2}利息]	    
    ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.利息支出/A.AVG_BAL*12 END) [{2}利息_PER]		    
    --,0 [{2}AVG_BAL_PER]
	,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.FTP息 END) [{2}FTP]		
	,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.FTP息/A.AVG_BAL*360/{1} END) [{2}FTP_PER]	
";
            fld = getDateRange(p_year, fld, true);

            return string.Format(@"
SELECT
	A.幣別
    {0}	
FROM
(
	SELECT 
		ISNULL(A.年月,'999999') AS YYYYMM
		,ISNULL(a.幣別,'ZZZ') AS 幣別
		,SUM(AVG_BAL) AS AVG_BAL
		,SUM(misdb.dbo.GetInterestExpense(a.AVG_BAL,a.AVG_RATE,a.幣別,a.存款類別,a.年月)) AS 利息支出
		,SUM(misdb.dbo.GetFtpRate(a.幣別,a.存款類別,a.AVG_BAL,a.FTP_RATE,a.年月))AS FTP息
	  FROM wmg.dbo.wmg001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
      WHERE (A.年月  BETWEEN @YM1 AND @YM2)
      AND A.存款類別 = '{1}'
      AND A.分行別 = '999'
      AND A.幣別 IN({2})
	GROUP BY CUBE(A.年月,A.幣別)
)A
GROUP BY A.幣別
ORDER BY A.幣別
", fld, p_tblName,wmgCUR);
        }


        /// <summary>
        /// WMG 使用外定外活佔比
        /// </summary>
        /// <param name="p_tblName">外匯活期、外匯定期</param>
        /// <param name="p_year"></param>
        /// <returns></returns>
        protected string getWMGOccupySQLStatement(string p_tblName, string p_year)
        {
            return getOccupySQLStatement(p_tblName, p_year,
@"SELECT
	A.CUR_CODE AS [Curr Code]
    {0}
FROM
(
	SELECT 
		A.年月 AS YYYYMM
		,A.幣別 AS CUR_CODE
		,CONVERT(NVARCHAR, ROUND(A.TOT/B.TOT,4)) [PERCENT]
		,B.TOT
	FROM (
			SELECT 
				A.年月
				,A.幣別
				,SUM(A.AVG_BAL) AS TOT
			FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
			WHERE (A.年月  BETWEEN @YM1 AND @YM2)
			AND A.分行別 = '999'
			AND A.存款類別 = '{2}'
			AND A.幣別 IN({3})
			GROUP BY A.年月,A.幣別
		)A LEFT JOIN (
					SELECT 
						A.年月
						,SUM(A.AVG_BAL) AS TOT
					FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
					WHERE (A.年月  BETWEEN @YM1 AND @YM2)
					AND A.分行別 = '999'
					AND A.存款類別 = '{2}'
					AND A.幣別 IN({3})
					GROUP BY A.年月
					)B ON A.年月 = B.年月
)A GROUP BY A.CUR_CODE

UNION ALL

SELECT
	'月均額' [Curr Code]
	{1}
		
FROM
(				
	SELECT 
		A.年月 AS YYYYMM
		,CONVERT(NVARCHAR,SUM(A.AVG_BAL)) AS TOT
	FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
	WHERE (A.年月  BETWEEN @YM1 AND @YM2)
	AND A.分行別 = '999'
	AND A.存款類別 = '{2}'
	AND A.幣別 IN({3})
	GROUP BY A.年月
)A");
        }
        protected string getWMGOccupySQLStatement(string p_tblName, string[] p_year)
        {
            return string.Format(
@"SELECT
	A.CUR_CODE AS [Curr Code]
    {0}
FROM
(
	SELECT 
		A.年月 AS YYYYMM
		,A.幣別 AS CUR_CODE
		,CONVERT(NVARCHAR, ROUND(A.TOT/B.TOT,4)) [PERCENT]
		,B.TOT
	FROM (
			SELECT 
				A.年月
				,A.幣別
				,SUM(A.AVG_BAL) AS TOT
			FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
			WHERE (A.年月  BETWEEN @YM1 AND @YM2)
			AND A.分行別 = '999'
			AND A.存款類別 = '{2}'
			AND A.幣別 IN({3})
			GROUP BY A.年月,A.幣別
		)A LEFT JOIN (
					SELECT 
						A.年月
						,SUM(A.AVG_BAL) AS TOT
					FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
					WHERE (A.年月  BETWEEN @YM1 AND @YM2)
					AND A.分行別 = '999'
					AND A.存款類別 = '{2}'
					AND A.幣別 IN({3})
					GROUP BY A.年月
					)B ON A.年月 = B.年月
)A GROUP BY A.CUR_CODE

UNION ALL

SELECT
	'月均額' [Curr Code]
	{1}
		
FROM
(				
	SELECT 
		A.年月 AS YYYYMM
		,CONVERT(NVARCHAR,SUM(A.AVG_BAL)) AS TOT
	FROM wmg.dbo.WMG001 AS A --WITH(INDEX(IX_C_DD4_DD_YM)) 
	WHERE (A.年月  BETWEEN @YM1 AND @YM2)
	AND A.分行別 = '999'
	AND A.存款類別 = '{2}'
	AND A.幣別 IN({3})
	GROUP BY A.年月
)A"
, getDateRange(p_year
        , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.[PERCENT]  END) [{1}]"
            + System.Environment.NewLine
        , false)
    , getDateRange(p_year
        , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.TOT  END) [{1}TOT]"
            + System.Environment.NewLine
        , false)
    , p_tblName
    ,wmgCUR
);
        }
        protected string getOccupySQLStatement(string p_tblName, string p_year
            , string p_sql)
        {           
            return string.Format(p_sql, getTwelveMonth(p_year
        , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.[PERCENT]  END) [{1}]"
            + System.Environment.NewLine
        , false)
    , getTwelveMonth(p_year
        , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.TOT  END) [{1}TOT]"
            + System.Environment.NewLine
        , false)
    , p_tblName,wmgCUR
   );
        }

        /// <summary>
        /// 取得佔比的外定和外存
        /// </summary>
        /// <returns></returns>
        protected string getOccupySQLStatement(string p_tblName, string p_year)
        {
            /*	
    ,MAX(CASE WHEN A.YYYYMM = '201102' THEN A.[PERCENT]  END) [201102]
	,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.[PERCENT]  END) [201101]
	,MAX(CASE WHEN A.YYYYMM = '201012' THEN A.[PERCENT]  END) [201012]
	,MAX(CASE WHEN A.YYYYMM = '201011' THEN A.[PERCENT]  END) [201011]
	,MAX(CASE WHEN A.YYYYMM = '201010' THEN A.[PERCENT]  END) [201010]
	,MAX(CASE WHEN A.YYYYMM = '201009' THEN A.[PERCENT]  END) [201009]
	,MAX(CASE WHEN A.YYYYMM = '201008' THEN A.[PERCENT]  END) [201008]
	,MAX(CASE WHEN A.YYYYMM = '201007' THEN A.[PERCENT]  END) [201007]
	,MAX(CASE WHEN A.YYYYMM = '201006' THEN A.[PERCENT]  END) [201006]
	,MAX(CASE WHEN A.YYYYMM = '201005' THEN A.[PERCENT]  END) [201005]
	,MAX(CASE WHEN A.YYYYMM = '201004' THEN A.[PERCENT]  END) [201004]
	,MAX(CASE WHEN A.YYYYMM = '201003' THEN A.[PERCENT]  END) [201003]
	,MAX(CASE WHEN A.YYYYMM = '201002' THEN A.[PERCENT]  END) [201002]
	,MAX(CASE WHEN A.YYYYMM = '201001' THEN A.[PERCENT]  END) [201001]
             * 
             * ,MAX(CASE WHEN A.YYYYMM = '201102' THEN A.TOT  END) [201102]
	,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.TOT  END) [201101]
	,MAX(CASE WHEN A.YYYYMM = '201012' THEN A.TOT  END) [201012]
	,MAX(CASE WHEN A.YYYYMM = '201011' THEN A.TOT  END) [201011]
	,MAX(CASE WHEN A.YYYYMM = '201010' THEN A.TOT  END) [201010]
	,MAX(CASE WHEN A.YYYYMM = '201009' THEN A.TOT  END) [201009]
	,MAX(CASE WHEN A.YYYYMM = '201008' THEN A.TOT  END) [201008]
	,MAX(CASE WHEN A.YYYYMM = '201007' THEN A.TOT  END) [201007]
	,MAX(CASE WHEN A.YYYYMM = '201006' THEN A.TOT  END) [201006]
	,MAX(CASE WHEN A.YYYYMM = '201005' THEN A.TOT  END) [201005]
	,MAX(CASE WHEN A.YYYYMM = '201004' THEN A.TOT  END) [201004]
	,MAX(CASE WHEN A.YYYYMM = '201003' THEN A.TOT  END) [201003]
	,MAX(CASE WHEN A.YYYYMM = '201002' THEN A.TOT  END) [201002]
	,MAX(CASE WHEN A.YYYYMM = '201001' THEN A.TOT  END) [201001]
             * {0} PERCENT
             * {1} TOT
             * {2} TABLE NAME 外活[C_DD4_DD_FX] 外定[C_TD4_TD_FX] 
             */
            return getOccupySQLStatement(p_tblName,p_year,@"
SELECT
	A.[Curr Code]
    {0}
FROM
(
	SELECT 
		A.YYYYMM
		,A.[Curr Code]
		,CONVERT(NVARCHAR, ROUND(A.TOT/B.TOT,4)) [PERCENT]
		,B.TOT
	FROM (
			SELECT 
				A.YYYYMM
				,A.[Curr Code]
				,SUM(A.[Avg Bal]) AS TOT
			FROM {2} 
			WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
			GROUP BY A.YYYYMM,A.[Curr Code]
		)A LEFT JOIN (
					SELECT 
						A.YYYYMM
						,SUM(A.[Avg Bal]) AS TOT
					FROM {2} 
					WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
					GROUP BY A.YYYYMM
					)B ON A.YYYYMM = B.YYYYMM
)A GROUP BY A.[Curr Code]

UNION ALL

SELECT
	'月均額' [Curr Code]
	{1}		
FROM
(				
	SELECT 
		A.YYYYMM
		,CONVERT(NVARCHAR,SUM(A.[Avg Bal])) AS TOT
	FROM {2} 
	WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
	GROUP BY A.YYYYMM
)A");
        }
        protected string getOccupySQLStatement(string p_tblName, string[] p_year)
        {
            /*	
    ,MAX(CASE WHEN A.YYYYMM = '201102' THEN A.[PERCENT]  END) [201102]
	,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.[PERCENT]  END) [201101]
	,MAX(CASE WHEN A.YYYYMM = '201012' THEN A.[PERCENT]  END) [201012]
	,MAX(CASE WHEN A.YYYYMM = '201011' THEN A.[PERCENT]  END) [201011]
	,MAX(CASE WHEN A.YYYYMM = '201010' THEN A.[PERCENT]  END) [201010]
	,MAX(CASE WHEN A.YYYYMM = '201009' THEN A.[PERCENT]  END) [201009]
	,MAX(CASE WHEN A.YYYYMM = '201008' THEN A.[PERCENT]  END) [201008]
	,MAX(CASE WHEN A.YYYYMM = '201007' THEN A.[PERCENT]  END) [201007]
	,MAX(CASE WHEN A.YYYYMM = '201006' THEN A.[PERCENT]  END) [201006]
	,MAX(CASE WHEN A.YYYYMM = '201005' THEN A.[PERCENT]  END) [201005]
	,MAX(CASE WHEN A.YYYYMM = '201004' THEN A.[PERCENT]  END) [201004]
	,MAX(CASE WHEN A.YYYYMM = '201003' THEN A.[PERCENT]  END) [201003]
	,MAX(CASE WHEN A.YYYYMM = '201002' THEN A.[PERCENT]  END) [201002]
	,MAX(CASE WHEN A.YYYYMM = '201001' THEN A.[PERCENT]  END) [201001]
             * 
             * ,MAX(CASE WHEN A.YYYYMM = '201102' THEN A.TOT  END) [201102]
	,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.TOT  END) [201101]
	,MAX(CASE WHEN A.YYYYMM = '201012' THEN A.TOT  END) [201012]
	,MAX(CASE WHEN A.YYYYMM = '201011' THEN A.TOT  END) [201011]
	,MAX(CASE WHEN A.YYYYMM = '201010' THEN A.TOT  END) [201010]
	,MAX(CASE WHEN A.YYYYMM = '201009' THEN A.TOT  END) [201009]
	,MAX(CASE WHEN A.YYYYMM = '201008' THEN A.TOT  END) [201008]
	,MAX(CASE WHEN A.YYYYMM = '201007' THEN A.TOT  END) [201007]
	,MAX(CASE WHEN A.YYYYMM = '201006' THEN A.TOT  END) [201006]
	,MAX(CASE WHEN A.YYYYMM = '201005' THEN A.TOT  END) [201005]
	,MAX(CASE WHEN A.YYYYMM = '201004' THEN A.TOT  END) [201004]
	,MAX(CASE WHEN A.YYYYMM = '201003' THEN A.TOT  END) [201003]
	,MAX(CASE WHEN A.YYYYMM = '201002' THEN A.TOT  END) [201002]
	,MAX(CASE WHEN A.YYYYMM = '201001' THEN A.TOT  END) [201001]
             * {0} PERCENT
             * {1} TOT
             * {2} TABLE NAME 外活[C_DD4_DD_FX] 外定[C_TD4_TD_FX] 
             */
            return string.Format(@"
SELECT
	A.[Curr Code]
    {0}
FROM
(
	SELECT 
		A.YYYYMM
		,A.[Curr Code]
		,CONVERT(NVARCHAR, ROUND(A.TOT/B.TOT,4)) [PERCENT]
		,B.TOT
	FROM (
			SELECT 
				A.YYYYMM
				,A.[Curr Code]
				,SUM(A.[Avg Bal]) AS TOT
			FROM {2} 
			WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
			GROUP BY A.YYYYMM,A.[Curr Code]
		)A LEFT JOIN (
					SELECT 
						A.YYYYMM
						,SUM(A.[Avg Bal]) AS TOT
					FROM {2} 
					WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
					GROUP BY A.YYYYMM
					)B ON A.YYYYMM = B.YYYYMM
)A GROUP BY A.[Curr Code]

UNION ALL

SELECT
	'月均額' [Curr Code]
	{1}		
FROM
(				
	SELECT 
		A.YYYYMM
		,CONVERT(NVARCHAR,SUM(A.[Avg Bal])) AS TOT
	FROM {2} 
	WHERE (A.YYYYMM BETWEEN @YM1 AND @YM2)
	GROUP BY A.YYYYMM
)A", getDateRange(p_year
        , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.[PERCENT]  END) [{1}]"
            + System.Environment.NewLine
        , false)
    , getDateRange(p_year
        , ",MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.TOT  END) [{1}TOT]"
            + System.Environment.NewLine
        , false)
    , p_tblName
   );
        }
    }
}
